//
//  CLSqliteManager.swift
//  CLWeibo
//
//  Created by 陈亮 on 16/7/23.
//  Copyright © 2016年 陈亮. All rights reserved.
//

import UIKit
//数据库名字
private let dbName = "Weibo.db"
//数据库路径
private let dbPath = (NSSearchPathForDirectoriesInDomains(.DocumentDirectory, .UserDomainMask, true).last! as NSString).stringByAppendingPathComponent(dbName)
//数据库操作类
class CLSqliteManager: NSObject {

    static let manager:CLSqliteManager = CLSqliteManager()
    //懒加载数据库操作队列
    lazy var queue = FMDatabaseQueue(path: dbPath)
    //构造函数私有化
    private override init() {
        super.init()
        print(dbPath)
        creatTables()
    }
    //创建表
    private func creatTables(){
        let sqlPath = NSBundle.mainBundle().pathForResource("db.sql", ofType: nil)!
        //读取文件内容
        let sql = try! String(contentsOfFile: sqlPath)
        //执行sql语句
        queue.inDatabase { (db) -> Void in
            let result = db.executeStatements(sql)
            if result {
                print("插入成功")
            }else {
                print("插入失败")
            }
        }
    }
    //查询数据
    func select(){
        //准备sql 语句
        let sql = "SELECT ID,NAME,AGE FROM T_PERSON"
        //执行sql 语句
        queue.inDatabase { (db) -> Void in
            let resultSet = db.executeQuery(sql, withArgumentsInArray: nil)
            
            while resultSet.next() {
                //获取id
                let id = resultSet.intForColumn("ID")
                //获取名字
                let name = resultSet.stringForColumn("NAME")
                //获取年龄
                let age = resultSet.intForColumn("AGE")
            }
        }
    }
    //通过sql语句查询返回的字典数组
    func queryResultSet(sql:String) -> [[String:AnyObject]] {
        
        //定义一个空的临时数据
        var tempArray = [[String:AnyObject]]()
        queue.inDatabase { (db) -> Void in
            let resultSet = db.executeQuery(sql, withArgumentsInArray: nil)
            while resultSet.next() {
                var dic = [String:AnyObject]()
                for i in 0..<resultSet.columnCount(){
                    //获取列名
                    let colName = resultSet.columnNameForIndex(i)
                    //获取列值
                    let colValue = resultSet.objectForColumnIndex(i)
                    //键值对添加到字典
                    dic.updateValue(colValue, forKey: colName)
                    
                }
                tempArray.append(dic)
            }
        }
        return tempArray
    }
    
    //修改语句
//    func update (){
//        //准备sql
//        let sql = "UPDATE T_PERSON SET NAME = ? WHERE ID = ?"
//        //修改所执行的sql语句
////        let sql = "DELETE FROM T_PEROSN WHERER ID = 1"
//        //执行sql
//        queue.inDatabase { (db) -> Void in
//            let result = db.executeUpdate(sql, withArgumentsInArray: ["jimmy":1])
//            if result {
//                print("修改成功")
//            }else {
//                print("修改失败")
//            }
//        }
//    }
    
    
    
    
    
}
